package com.lee.sql;

import cn.hutool.core.lang.Singleton;
import cn.hutool.core.util.ObjectUtil;
import cn.hutool.core.util.StrUtil;
import com.lee.exception.DataSourceException;
import com.lee.meta.ColumnMeta;
import com.lee.meta.IndexColumnMeta;
import com.lee.meta.IndexMeta;
import com.lee.meta.TableMeta;
import com.lee.type.IndexTypeEnum;

import java.sql.Types;
import java.util.Comparator;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

/**
 * @description: SQL Server sql
 * @author: 青石路
 * @date: 2024/4/26 15:50
 */
public class SqlServerSql implements DBSql{

    // 使用 volatile 关键字保证可见性和禁止指令重排序
    private static volatile SqlServerSql instance;

    // 构造方法私有化，防止外部创建实例
    private SqlServerSql() {}

    // 提供一个全局访问点，使用双重检查锁定保证线程安全
    public static SqlServerSql getInstance() {
        if (instance == null) {
            synchronized (Singleton.class) {
                if (instance == null) {
                    instance = new SqlServerSql();
                }
            }
        }
        return instance;
    }

    /**
     * 该sql执行后返回的结果有多个
     * @param queryParam 查询参数
     * @return
     */
    @Override
    public String getTableDDLSql(TableQueryParam queryParam) {
        return StrUtil.format("EXEC sp_help '{}.{}.{}'", queryParam.getCatalog(), queryParam.getSchema(), queryParam.getTableName());
    }

    @Override
    public String getTableColumnSql(TableQueryParam queryParam) {
        return StrUtil.format("SELECT * FROM {}.{}.{} WHERE 1=0", queryParam.getCatalog(), queryParam.getCatalog(), queryParam.getTableName());
    }

    @Override
    public String getMaxValueSql(TableQueryParam queryParam, String columnName) {
        return StrUtil.format("SELECT MAX({}) FROM {}.{}.{}", queryParam.getCatalog(), queryParam.getCatalog(), queryParam.getTableName());
    }

    @Override
    public String getCreateTableSql(TableQueryParam queryParam, TableMeta tableMeta, List<ColumnMeta> columnMetas, IndexMeta primaryKeyMeta,
                                    Map<String, IndexMeta> indexMetaMap) {
        StringBuilder createSql = new StringBuilder("CREATE TABLE " + queryParam.getTargetCatalog() + "."
                + queryParam.getTargetSchema() + "." + queryParam.getTargetTableName() + " ( ");
        for (ColumnMeta columnMeta : columnMetas) {
            createSql.append(columnMeta.getColumnName()).append(" ").append(getColumnType(columnMeta));
            if (columnMeta.getIfAutoIncrement()) {
                createSql.append(" IDENTITY(1,1)");
            }
            // 暂不处理默认值
            /*if (StrUtil.isNotBlank(columnMeta.getDefaultValue())) {
                createSql.append(" DEFAULT '").append(columnMeta.getDefaultValue()).append("'");
            }*/
            if (columnMeta.getIfNullable() == 0) {
                createSql.append(" NOT NULL");
            }
            createSql.append(",");
        }
        // 主键处理
        if (ObjectUtil.isNotNull(primaryKeyMeta)) {
            List<IndexColumnMeta> indexColumns = primaryKeyMeta.getIndexColumns();
            indexColumns.sort(Comparator.comparingInt(IndexColumnMeta::getOrdinalPosition));
            createSql.append(" PRIMARY KEY (");
            createSql
                    .append(indexColumns.stream().map(IndexColumnMeta::getColumnName).collect(Collectors.joining(", ")));
            createSql.append("),");
        }
        // 索引处理
        for (IndexMeta indexMeta : indexMetaMap.values()) {
            if (indexMeta.getIndexType() == IndexTypeEnum.UNIQUE) {
                if (StrUtil.isNotBlank(indexMeta.getIndexName())) {
                    createSql.append("CONSTRAINT ").append(indexMeta.getIndexName()).append(" ");
                }
                createSql.append("UNIQUE ");
            } else {
                createSql.append("INDEX ");
                if (StrUtil.isNotBlank(indexMeta.getIndexName())) {
                    createSql.append(indexMeta.getIndexName()).append(" ");
                }
            }
            List<IndexColumnMeta> indexColumns = indexMeta.getIndexColumns();
            indexColumns.sort(Comparator.comparingInt(IndexColumnMeta::getOrdinalPosition));
            createSql.append("(")
                    .append(indexColumns.stream().map(IndexColumnMeta::getColumnName).collect(Collectors.joining(", ")))
                    .append("),");
        }
        // 删除最后一个逗号
        createSql.deleteCharAt(createSql.length() - 1);
        createSql.append(")");
        // TODO 注释处理
        return createSql.toString();
    }

    /**
     * 获取表 字段类型
     *
     * @param columnMeta 列元数据
     * @return sql server 字段类型
     */
    private String getColumnType(ColumnMeta columnMeta) {
        switch (columnMeta.getColumnType()) {
            // 数值类型
            case Types.TINYINT:
                return "TINYINT";
            case Types.SMALLINT:
                return "SMALLINT";
            case Types.INTEGER:
                return "INT";
            case Types.BIGINT:
                return "BIGINT";
            case Types.FLOAT:
            case Types.REAL:
            case Types.DOUBLE:
                return columnMeta.getPrecision() > 24 ? "FLOAT(53)" : "REAL";
            case Types.DECIMAL:
                return "DECIMAL(" + columnMeta.getPrecision() + "," + columnMeta.getScale() + ")";
            case Types.NUMERIC:
                return columnMeta.getScale() <= 0 ? "BIGINT"
                        : "DECIMAL(" + columnMeta.getPrecision() + "," + columnMeta.getScale() + ")";
            // 字符与字符串类型
            case Types.CHAR:
            case Types.NCHAR:
                return columnMeta.getPrecision() > 0 ? "NCHAR(" + columnMeta.getPrecision() + ")" : "NCHAR";
            case Types.VARCHAR:
            case Types.NVARCHAR:
                return columnMeta.getPrecision() > 0 ? "NVARCHAR(" + columnMeta.getPrecision() + ")" : "NVARCHAR";
            case Types.LONGVARCHAR:
            case Types.LONGNVARCHAR:
            case Types.CLOB:
            case Types.NCLOB:
                return "NVARCHAR(MAX)";
            // 日期和时间类型
            case Types.DATE:
                return "DATE";
            case Types.TIME:
                return "TIME" + (columnMeta.getPrecision() > 8 ? "(" + (columnMeta.getPrecision() - 9) + ")" : "");
            case Types.TIMESTAMP:
                switch (columnMeta.getColumnTypeName()) {
                    case "DATETIME":
                        return "DATETIME2"
                                + (columnMeta.getPrecision() > 19 ? "(" + (columnMeta.getPrecision() - 20) + ")" : "");
                    case "DATE":
                        // oracle 的 DATE
                        return "DATETIME2";
                    default:
                        return "SMALLDATETIME";
                }
                // 二进制类型
            case Types.BIT:
            case Types.BOOLEAN:
                return "BIT";
            case Types.BINARY:
                switch (columnMeta.getColumnTypeName()) {
                    case "TIMESTAMP":
                        return "TIMESTAMP";
                    default:
                        return columnMeta.getPrecision() > 0 ? "BINARY(" + columnMeta.getPrecision() + ")" : "BINARY";
                }
            case Types.VARBINARY:
            case Types.BLOB:
            case Types.LONGVARBINARY:
                return "VARBINARY(MAX)";
            case Types.OTHER:
                if (columnMeta.getColumnTypeName().contains("VARCHAR")) {
                    return "NVARCHAR" + (columnMeta.getPrecision() > 0 ? "(" + columnMeta.getPrecision() + ")" : "");
                } else if (columnMeta.getColumnTypeName().contains("TIMESTAMP")) {
                    return "SMALLDATETIME";
                } else {
                    throw new DataSourceException("不支持的类型：" + columnMeta.getColumnTypeName());
                }
            default:
                throw new DataSourceException("不支持的类型：" + columnMeta.getColumnTypeName());
        }
    }
}
